package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Project;
import cn.edu.lingnan.pojo.ProjectAdult;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProjectDaoImp implements ProjectDao {
    @Override
    public List<Project> queryAllProjects(Integer page, Integer pageSize) {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select * from school.project as pr join school.posts as po on pr.posts_id = po.posts_id LIMIT ?, ?");

            ps.setInt(1, (page - 1) * pageSize);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
                projectList.add(project);
            }
            // 关闭资源
            rs.close();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public List<Project> queryMyProjects(String sname) {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.project AS pr JOIN school.posts AS po ON pr.posts_id = po.posts_id WHERE pr.members  LIKE ? and status = 1");
            ps.setString(1, "%" +"队长:"+sname + "%");

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
                projectList.add(project);
            }
            // 关闭资源
            rs.close();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public List<Project> queryNoProjects(String sname) {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            // 建立会话
            String sql = "SELECT * FROM school.project AS pr " +
                    "JOIN school.posts AS po ON pr.posts_id = po.posts_id " +
                    "LEFT JOIN school.adult AS ad ON pr.project_id = ad.project_id " +
                    "WHERE pr.members NOT LIKE ? " +
                    "AND pr.project_id NOT IN (" +
                    "    SELECT project_id FROM school.adult WHERE cname LIKE ? and status = 0" +
                    ")";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + sname + "%");
            ps.setString(2, "%" + sname + "%");

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
                projectList.add(project);
            }
            // 关闭资源
            rs.close();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public Project queryProjectById(Integer projectId) {
        Project project = null;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.project AS pr JOIN school.posts AS po ON pr.posts_id = po.posts_id WHERE pr.project_id = ?");
            ps.setInt(1, projectId);

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectIded = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                project = new Project(projectIded, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
            }
            // 关闭资源
            rs.close();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return project;
    }

    @Override
    public Integer StudentInsertProject(String sname, Integer projectId, Integer userId) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("insert into school.adult (project_id, cname,status ,sid) values(?,?,?,?)");
            ps.setInt(1, projectId);
            ps.setString(2, sname);
            ps.setInt(3, 0);
            ps.setInt(4, userId);

            count = ps.executeUpdate();
            // 关闭资源
            ps.close();
            conn.close();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public Integer AdminInsertProject(Project project) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("insert into school.project (project_id, posts_id, pname, type, start, end, status, description, members) values(?,?,?,?,?,?,?,?,?)");
            ps.setInt(1, project.getProtectId());
            ps.setInt(2, project.getPostId());
            ps.setString(3, project.getProjectName());

            ps.setString(4, project.getType());
            ps.setString(5, project.getStartTime());
            ps.setString(6, project.getEndTime());
            ps.setInt(7, 1);
            ps.setString(8, project.getDescription());
            ps.setString(9, project.getMembers());

            count = ps.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public Integer updateProject(Project project) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("update school.project set pname = ?, posts_id = ?, type = ?, start = ?, end = ?, status = ?, description = ?, members = ? where project_id = ?");
            ps.setString(1, project.getProjectName());
            ps.setInt(2, project.getPostId());
            ps.setString(3, project.getType());
            ps.setString(4, project.getStartTime());
            ps.setString(5, project.getEndTime());
            ps.setInt(6, project.getStatus());
            ps.setString(7, project.getDescription());
            ps.setString(8, project.getMembers());
            ps.setInt(9, project.getProtectId());

            count = ps.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public Integer deleteProject(Integer[] projectIds) {
        int rowsAffected = 0;

        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true",
                    "root", "123456"
            );

            // 设置autocommit为false
            conn.setAutoCommit(false);

            PreparedStatement psStudent = conn.prepareStatement("DELETE FROM school.project WHERE project_id = ?");
            for (Integer id : projectIds) {
                psStudent.setInt(1, id);
                psStudent.addBatch(); // 将删除操作添加到批处理
            }
            int[] results = psStudent.executeBatch(); // 执行批处理

            // 计算受影响的行数
            for (int result : results) {
                rowsAffected += result;
            }

            // 提交事务
            conn.commit();

            psStudent.close();
            conn.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

        return rowsAffected;
    }

    @Override
    public List<Project> QueryStudnetCreateProject() {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.project AS pr JOIN school.posts AS po ON pr.posts_id = po.posts_id WHERE status = 0");

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
                projectList.add(project);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public Integer AgreeProject(Integer projectId, Integer status) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("update school.project set status = ? where project_id = ?");
            ps.setInt(1, status);
            ps.setInt(2, projectId);


            count = ps.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public Integer RejectedProject(Integer projectId, Integer status, String reason) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("update school.project set status = ? ,rejected=? where project_id = ?");
            ps.setInt(1, -1);
            ps.setString(2, reason);
            ps.setInt(3, projectId);


            count = ps.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public List<Project> QueryRejectedProject(String sname) {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.project AS pr JOIN school.posts AS po ON pr.posts_id = po.posts_id WHERE pr.members  LIKE ? and status = -1");
            ps.setString(1, "%" +"队长:"+sname + "%");

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                String rejected = rs.getString("rejected");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,rejected);
                projectList.add(project);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public Integer countProject() {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select count(*) from school.project");

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public List<Project> queryProjectLikeName(String sname) {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.project AS pr JOIN school.posts AS po ON pr.posts_id = po.posts_id WHERE pr.pname LIKE ?");
            ps.setString(1, "%" + sname + "%");

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
                projectList.add(project);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public Integer deleteInsertProject(Integer projectId) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("delete from school.adult where project_id = ?");

            ps.setInt(1, projectId);

            count = ps.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public List<ProjectAdult> queryaduitProject() {
        List<ProjectAdult> projectList = new ArrayList<ProjectAdult>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            String sql = "SELECT * " +
                    "FROM school.project pr " +
                    "JOIN school.adult ad ON ad.project_id =pr.project_id " +
                    "JOIN school.posts po ON pr.posts_id = po.posts_id  where ad.status = 0";
            PreparedStatement ps = conn.prepareStatement(sql);
            //"select * from school.project as pr join school.adult as ad on pr.project_id = ad.project_id where ad.status = 0"
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String sname = rs.getString("cname");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                ProjectAdult project = new ProjectAdult(projectId, pname, pid, postsName, sname, type, startTime, endTime, status, description, members);
                projectList.add(project);

            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public List<ProjectAdult> studentqueryaduitProject(Integer userId) {
        List<ProjectAdult> projectList = new ArrayList<ProjectAdult>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            String sql = "SELECT * FROM school.project AS pr " +
                    "JOIN school.posts AS po ON pr.posts_id = po.posts_id " +
                    "LEFT JOIN school.adult AS ad ON pr.project_id = ad.project_id " +
                    "where pr.project_id  IN (" +
                    "    SELECT project_id FROM school.adult WHERE sid = ? and status =0 " +
                    ")";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);
            //"select * from school.project as pr join school.adult as ad on pr.project_id = ad.project_id where ad.status = 0"
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String sname = rs.getString("cname");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                System.out.println(status);
                String description = rs.getString("description");
                String members = rs.getString("members");
                ProjectAdult project = new ProjectAdult(projectId, pname, pid, postsName, sname, type, startTime, endTime, status, description, members);
                projectList.add(project);

            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public List<Project> querymyEnterProject(String sname) {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("select * from school.project JOIN school.posts on project.posts_id = posts.posts_id where members like ? and status = 1");
            ps.setString(1, "%" + sname + "%");

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
                projectList.add(project);

            }

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public Integer AgreeEnterProject(Integer projectId, String sname) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps1 = conn.prepareStatement("update school.project set members=CONCAT(members, ?) where project_id=? ");
            ps1.setString(1, "," + sname); // Assuming sname is the member name
            ps1.setInt(2, projectId);
            count = ps1.executeUpdate();

            PreparedStatement ps2 = conn.prepareStatement("update school.adult set status=1 where project_id=? ");
            ps2.setInt(1, projectId);
            ps2.executeUpdate();
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public Integer StudnetCreateProject(Project project) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("insert into school.project (project_id, posts_id, pname, type, start, end, status, description, members) values(?,?,?,?,?,?,?,?,?)");
            ps.setInt(1, project.getProtectId());
            ps.setInt(2, project.getPostId());
            ps.setString(3, project.getProjectName());

            ps.setString(4, project.getType());
            ps.setString(5, project.getStartTime());
            ps.setString(6, project.getEndTime());
            ps.setInt(7, 0);
            ps.setString(8, project.getDescription());
            ps.setString(9, project.getMembers());


            count = ps.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    @Override
    public List<Project> StudentQueryCreateAdultProject(String sname) {
        List<Project> projectList = new ArrayList<Project>();
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.project AS pr JOIN school.posts AS po ON pr.posts_id = po.posts_id WHERE pr.members  LIKE ? AND pr.status = 0");
            ps.setString(1, "%" + sname + "%");

            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Integer projectId = rs.getInt("project_id");
                String pname = rs.getString("pname");
                Integer pid = rs.getInt("posts_id");
                String postsName = rs.getString("posts_name");
                String type = rs.getString("type");
                String startTime = rs.getString("start");
                String endTime = rs.getString("end");
                Integer status = rs.getInt("status");
                String description = rs.getString("description");
                String members = rs.getString("members");
                Project project = new Project(projectId, pname, pid, postsName, type, startTime, endTime, status, description, members,"");
                projectList.add(project);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return projectList;
    }

    @Override
    public Integer StudentDeleteProjectCreateAdultProject(Integer projectId, String sname) {
        Integer count = 0;
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("delete from school.project where project_id = ? and members like ?");

            ps.setInt(1, projectId);
            ps.setString(2, "%" + sname + "%");

            count = ps.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return count;
    }

    public static void main(String[] args) {
        ProjectDaoImp projectDao = new ProjectDaoImp();
        List<Project> projects = projectDao.QueryStudnetCreateProject();
        for (Project project : projects) {
            System.out.println(project);
        }
    }
}
